IF OBJECT_ID('dbo.RptSubContractMetricSnapshotReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractMetricSnapshotReport
    IF OBJECT_ID('dbo.RptSubContractMetricSnapshotReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractMetricSnapshotReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractMetricSnapshotReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubContractMetricSnapshotReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/14/2008
--
-- Description: Returns summary of Deliverable Statuses by INST or Specialist
--
--
-- Parameters:  
--     @AsOfDate   
--     @Institution
--     @Department 
--     @Unit        
--     @Subcontractor 
--     @SubcontractSpecialist
--     @DomainUserId  
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptSubContractMetricSnapshotReport(
--                       @WeekEndDate             = '10/12/2008',
--                       @Institution             = '2,3',
--                       @Department              = NULL,
--                       @Unit                    = NULL,
--                       @Subcontractor           = NULL,
--                       @SubcontractSpecialist   = NULL,
--                       @GroupBy                 = NULL,
--                       @DomainUserId            = 11
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/14/08    KC        Created
-- 10/24/08    KC        Many interations, many changes, just about there
-- 12/06/08    KC        Remmoved ActiveYN column from table..
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubContractMetricSnapshotReport
     (
     @WeekEndDate                       DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(200) = NULL,
     @SubcontractSpecialist             VARCHAR(100) = NULL,
     @GroupBy                           VARCHAR(20) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT,
               @StartDate          DATETIME,
               @EndDate            DATETIME,
               @SrchInstitution    VARCHAR(50),
               @SrchDepartment     VARCHAR(50),
               @SrchUnit           VARCHAR(50)
     
     IF @GroupBy NOT IN ('ORG','SS') OR @GroupBy IS NULL
     BEGIN
          RAISERROR ('ERROR! GroupBy Parameter should be "ORG" or "SS", not "%s".',11,1,@GroupBy) 
          RETURN -1
     END
     
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL
     IF @SubcontractSpecialist IN ('ALL','','0') SET @SubcontractSpecialist = NULL


     -- We have to return some search parameter names so they can display in the headers.
     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0
          SELECT    TOP 1 @SrchInstitution = S.Institution
          FROM      dbo.SubContractDeliverableSnapShot S
          WHERE     S.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution)) 

     IF @Department IS NOT NULL AND CHARINDEX(',',@Department) = 0
          SELECT    TOP 1 @SrchDepartment = S.Department
          FROM      dbo.SubContractDeliverableSnapShot S
          WHERE     S.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department)) 

     IF @Unit IS NOT NULL AND CHARINDEX(',',@Unit) = 0
          SELECT    TOP 1 @SrchUnit = S.Unit
          FROM      dbo.SubContractDeliverableSnapShot S
          WHERE     S.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit)) 

     SELECT    G.GroupBy,
               G.GroupById,
               G.REPORTCAT,
               C.Code, 
               C.SortOrder,
               STA.Week4EndDate,
               ISNULL(STA.Week4,0) AS Week4,
               STA.Week3EndDate,
               ISNULL(STA.Week3,0) AS Week3,
               STA.Week2EndDate,
               ISNULL(STA.Week2,0) AS Week2,
               STA.Week1EndDate,
               ISNULL(STA.Week1,0) AS Week1,
               ISNULL(DUE.[30DaysDue],0) AS [30DaysDue],
               ISNULL(DUE.[60DaysDue],0) AS [60DaysDue],
               ISNULL(DUE.[90DaysDue],0) AS [90DaysDue],
               @SrchInstitution AS SrchInstitution,
               @SrchDepartment AS SrchDepartment,
               @SrchUnit AS SrchUnit
     FROM    dbo.fnGetMultiCriteriaCodeSort('UnExecutedAgreementRecd,PendingGA,PendingDocFromDeptPI,PendingDeptPIAppr,InQueue,Assigned,InNegotiation,PartiallyExecuted,FullyExecuted,OtherUnresolved,PendingOutYears') C
          FULL OUTER JOIN
          (SELECT DISTINCT 
                    CASE @GroupBy 
                         WHEN 'ORG' THEN S.Institution
                         ELSE ISNULL(S.Specialist,'[UnAssigned]')
                    END AS GroupBy,
                    CASE @GroupBy 
                         WHEN 'ORG' THEN InstitutionId
                         ELSE NULL
                    END AS GroupById,
                    REPORTCAT
          FROM      dbo.SubContractDeliverableSnapShot S
          WHERE     (@Institution IS NULL OR S.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
                    (@SubcontractSpecialist IS NULL OR S.Specialist = @SubcontractSpecialist) ) G ON 1=1
          
          LEFT OUTER JOIN
          (SELECT   S.REPORTSTAT, 
                    S.REPORTCAT,
                    S.GroupBy,
                    S.GroupById,
                    MAX(CASE WHEN S.WeekDiff = 4
                      THEN S.WeekEndDate
                      ELSE 0
                    END) AS Week4EndDate,
                    SUM(CASE WHEN S.WeekDiff = 4
                      THEN S.CNT
                      ELSE 0
                    END) AS Week4,
                    MAX(CASE WHEN S.WeekDiff = 3
                      THEN S.WeekEndDate
                      ELSE 0
                    END) AS Week3EndDate,
                    SUM(CASE WHEN S.WeekDiff = 3
                      THEN S.CNT
                      ELSE 0
                    END) AS Week3,
                    MAX(CASE WHEN S.WeekDiff = 2
                      THEN S.WeekEndDate
                      ELSE 0
                    END) AS Week2EndDate,
                    SUM(CASE WHEN S.WeekDiff = 2
                      THEN S.CNT
                      ELSE 0
                    END) AS Week2,
                    MAX(CASE WHEN S.WeekDiff = 1
                      THEN S.WeekEndDate
                      ELSE 0
                    END) AS Week1EndDate,
                    SUM(CASE WHEN S.WeekDiff = 1
                      THEN S.CNT
                      ELSE 0
                    END) AS Week1
          FROM (                   
               SELECT    CASE @GroupBy 
                              WHEN 'ORG' THEN Institution
                              ELSE ISNULL(S.Specialist,'[UnAssigned]')
                         END AS GroupBy,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN InstitutionId
                              ELSE NULL
                         END AS GroupById,
                         S.REPORTCAT,
                         S.REPORTSTAT,
                         --S.REPORTSTAT_CODE,
                         DATEDIFF(WK,S.WeekEndDate, @WeekEndDate) + 1 AS WeekDiff,
                         S.WeekEndDate,
                         COUNT(S.ReportId) AS CNT
               FROM      dbo.SubContractDeliverableSnapShot S
               WHERE     (S.WeekEndDate BETWEEN DATEADD(WK,-3,@WeekEndDate) AND @WeekEndDate) AND
                         (@Institution IS NULL OR S.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
                         (@Department IS NULL OR S.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
                         (@Unit IS NULL OR S.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
                         (@Subcontractor IS NULL OR S.SubContractorName = @Subcontractor) AND
                         (@SubcontractSpecialist IS NULL OR S.Specialist = @SubcontractSpecialist)
               GROUP BY  S.WeekEndDate,
                         S.REPORTCAT,
                         S.REPORTSTAT,
                         --S.REPORTSTAT_CODE,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN Institution
                              ELSE ISNULL(S.Specialist,'[UnAssigned]')
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN InstitutionId
                              ELSE NULL
                         END) S
          GROUP BY  S.REPORTCAT,
                    S.REPORTSTAT,
                    S.GroupBy,
                    S.GroupById) STA ON C.Code = STA.REPORTSTAT AND G.GroupBy = STA.GroupBy AND G.REPORTCAT = STA.REPORTCAT
                     
          LEFT OUTER JOIN
          (SELECT   D.REPORTSTAT,
                    D.REPORTCAT,
                    D.GroupBy,
                    D.GroupById,
                    SUM(CASE WHEN D.DayDiff BETWEEN 0 AND 30 
                         THEN D.CNT
                         ELSE 0
                    END) AS [30DaysDue],
                    SUM(CASE WHEN D.DayDiff BETWEEN 31 AND 60 
                         THEN D.CNT
                         ELSE 0
                    END) AS [60DaysDue],
                    SUM(CASE WHEN D.DayDiff BETWEEN 61 AND 90 
                         THEN D.CNT
                         ELSE 0
                    END) AS [90DaysDue]
                    
          FROM (         
               SELECT    CASE @GroupBy 
                              WHEN 'ORG' THEN Institution
                              ELSE ISNULL(S.Specialist,'[UnAssigned]')
                         END AS GroupBy,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN InstitutionId
                              ELSE NULL
                         END AS GroupById,
                         S.REPORTCAT,
                         S.REPORTSTAT,
                         --S.REPORTSTAT_CODE,
                         DATEDIFF(DD, @WeekEndDate, S.DueDate) AS DayDiff,
                         COUNT(S.ReportId) AS CNT
               FROM      dbo.SubContractDeliverableSnapShot S
               WHERE     (S.WeekEndDate = @WeekEndDate) AND
                         (S.DueDate BETWEEN @WeekEndDate AND DATEADD(DD,90,@WeekEndDate)) AND
                         (@Institution IS NULL OR S.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
                         (@Department IS NULL OR S.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
                         (@Unit IS NULL OR S.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
                         (@Subcontractor IS NULL OR S.SubContractorName = @Subcontractor) AND
                         (@SubcontractSpecialist IS NULL OR S.Specialist = @SubcontractSpecialist)
               GROUP BY  S.DueDate,
                         S.REPORTCAT,
                         S.REPORTSTAT,
                         --S.REPORTSTAT_CODE,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN Institution
                              ELSE ISNULL(S.Specialist,'[UnAssigned]')
                         END,
                         CASE @GroupBy 
                              WHEN 'ORG' THEN InstitutionId
                              ELSE NULL
                         END) D 
          GROUP BY  D.REPORTCAT,
                    D.REPORTSTAT,
                    D.GroupBy,
                    D.GroupById) DUE ON C.Code = DUE.REPORTSTAT AND G.GroupBy = DUE.GroupBy    AND G.REPORTCAT = DUE.REPORTCAT
     ORDER BY  G.GroupBy,
               G.REPORTCAT,
               C.SortOrder    
     
     -- check for errors
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error != 0 
     BEGIN
          RETURN -1
     END
     
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubContractMetricSnapshotReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractMetricSnapshotReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractMetricSnapshotReport >>>'
go

GRANT EXECUTE ON dbo.RptSubContractMetricSnapshotReport TO InsightReport,Insight
go


